package com.es.message;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import com.es.pay.mailAndMsg.HrmCtmBean;
import com.es.util.db.Dao;
import com.es.util.page.PageBean;
import com.es.util.page.PageNavigator;

public class MessageBiz {
	@SuppressWarnings("unchecked")
	public ArrayList<HrmCtmBean> getMenuAllList()throws Exception {

		Dao dao = new Dao();
		HrmCtmBean menu = null;
	    ArrayList<HrmCtmBean> menuList = new ArrayList<HrmCtmBean>();

	    String sql = " select distinct ltrim(rtrim(emp_area_code)) pid ,ltrim(rtrim(emp_code)) id, emp_name+'('+emp_role+')' name from hrm_emp_info where emp_shiyebu='ITMALL' and  emp_code is not null and emp_area_code is not null and emp_area_code!=''"
	    			+"	union all"
	    			+"	select distinct 'a0' pid,ltrim(rtrim(emp_area_code)) id , ltrim(rtrim(emp_area_value)) name from hrm_emp_info where emp_shiyebu='ITMALL' and emp_area_code is not null and emp_area_code!='' order by 1";
	    try {
	    	ResultSet rs = dao.Select(sql);
	      while (rs.next()){
	    	menu = new HrmCtmBean();
	        menu.setId(rs.getString("id"));
	        menu.setPid(rs.getString("pid"));
	        menu.setName(rs.getString("name"));
	        menuList.add(menu);
	      }
	    }
	    finally {
	    	dao.close();
	    }
	   /* if(menuList.size()==0)
	    	return null;*/
	    return menuList;
	}
	
	/**
	 * 获取邮件and 电话地址
	 * @return
	 * @throws Exception
	 */
	public ArrayList<HashMap<String,String>> getMsgList(String ctmId,String content) throws Exception{
		Dao dao = new Dao();
		ArrayList<HashMap<String,String>> list = new ArrayList<HashMap<String,String>>();
		ArrayList<String> queryParm = new ArrayList<String>();
	    queryParm.add(ctmId);
		try {
			String sql = "select  emp_code,emp_phone_mobile from hrm_emp_info where charindex(emp_code+',',?)>0 ";
			ResultSet resultSet = dao.Select(sql,queryParm);
			while(resultSet.next()){
				HashMap<String,String> map = new HashMap<String,String>();
				map.put("emp_code", resultSet.getString("emp_code"));
				map.put("emp_phone_mobile", resultSet.getString("emp_phone_mobile"));
				map.put("real_salary", content);
				list.add(map);
			  }
		} finally{
			dao.close();
		}
		return list;
	}

	public PageBean queryEsgList(PageNavigator pageNav, ArrayList<String> param)
	throws Exception {
		Dao dao = new Dao();
		PageBean pageBean = new PageBean();
		ArrayList<String> query = new ArrayList<String>();
		try {
			String sql = "select emp_code,phone,msg,create_date,result ,(select top 1 emp_id from hrm_emp_info where hrm_emp_info.emp_code = hrm_msg_history.emp_code) emp_id from hrm_msg_history where '1'='1'";
			String[] sqlArrary = new String[] { 
					"and UPPER(emp_code) like '%'+UPPER(?)+'%'",
					"and UPPER(phone) like '%'+UPPER(?)+'%'",
					"and UPPER(msg) like '%'+UPPER(?)+'%'",
					"and UPPER(queryDay) like '%'+UPPER(?)+'%'"};
			for (int i = 0; i < param.size(); ++i) {
				if (!param.get(i).equals("")&&i<8) {
						sql += sqlArrary[i];
						query.add(param.get(i).trim());
				}
			}
			int total = dao.getAllRow(sql, query);
			pageNav.setRowTotal(total);
			pageNav.setPageTotal(total % pageNav.getPageRow() == 0 ? total
					/ pageNav.getPageRow() : total / pageNav.getPageRow() + 1);
			pageBean = dao.queryPageList(sql, pageNav, query);
		} finally {
			dao.close();
		}
		return pageBean;
		}
}
